<?php
declare (strict_types=1);

namespace app\util;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class ExcelHandle
{
    /**
     * @func downloadExcel
     * @desc 导出excel
     * @param $name excel名称
     * @param $titles 标题
     * @param array $data
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @Time: 2021/9/23 14:48
     */
    public static function downloadExcel($name, $titles, $data=[]) {
        $expFileName = $name.'.xlsx';
        //设置保存路径
        $basePath = root_path().'public';
        $baseUrl  = DIRECTORY_SEPARATOR .implode(DIRECTORY_SEPARATOR, ['excel',date('Y-m-d',time())]). DIRECTORY_SEPARATOR;
        $path = $basePath.$baseUrl;
        //路径不存在则创建路径
        if(!is_dir($path)){
            mkdir($path, 0777, true);
        }
        $path = $path.$expFileName;

        $count = count($titles);  //计算表头数量
        $spreadsheet = new Spreadsheet();
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER_CONTINUOUS,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
        ];
        $sheet = $spreadsheet->getActiveSheet();

        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头
            $sheet->getStyle(strtoupper(chr($i)))->applyFromArray($styleArray);
            $sheet->getCell(strtoupper(chr($i)).'1')->getStyle()->getFont()->setBold(true);
            $index = $i - 65;
            $sheet->setCellValue(strtoupper(chr($i)) . '1',  $titles[$index][key($titles[$index])] );
            $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20);
        }

        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        foreach ($data as $key => $item) {             //循环设置单元格：
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写
            for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始：
                $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2),$item[key($titles[$i - 65])]);
            }
        }
        ob_end_clean();//清除缓冲区,避免乱码
        // $writer = IOFactory::createWriter($spreadsheet,'Xlsx');
        // $writer->save($path);
        // $filePath = str_replace('\\','/',$baseUrl.$expFileName);
        // return $filePath;

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet,'Xlsx');
        $writer->save('php://output');
        //删除清空
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    /**
     * 导入excel
     * @param $filePath 文件路径
     * @param $cols 要导入的列 从1开始 $cols = [['1' => 'id'],['2' =>'name']];
     * @param string $readerType excel类型
     * @param $rows 要导入的行，默认从2开始;
     * @param int $sheetIndex sheet索引,默认从0开始
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function importExcel($filePath, $cols, $readerType = 'Xlsx', $startRows = 2, $sheetIndex = 0): array {
        $reader = IOFactory::createReader(ucfirst($readerType));
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($filePath); //载入excel表格

        $sheetCount =  $spreadsheet->getSheetCount();
        if ($sheetIndex >= $sheetCount){
            throw new \Exception('sheet不存在');
        }
        $worksheet = $spreadsheet->getSheet($sheetIndex);
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestDataColumn(); // 总列数
        $lines = $highestRow - ($startRows - 1);
        if ($lines <= 0) {
            throw new \Exception('Excel表格中没有数据');
        }
        $data = [];
        for ($row = $startRows; $row <= $highestRow; $row++) {
            $rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,NULL,TRUE,FALSE);
            if(self::isEmptyRow(reset($rowData))) { continue; } // 整行为空跳过
            for ($i = 0; $i < count($cols); $i ++){
                $index = key($cols[$i]);
                $key = array_values($cols[$i])[0];
                $data[$row][$key] = $worksheet->getCellByColumnAndRow($index, $row)->getValue();
            }
        }
        return array_values($data);
    }

    public static function isEmptyRow($row) {
        foreach($row as $cell){
            if (null !== $cell) return false;
        }
        return true;
    }
}